﻿using System;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Ink;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using VAdvantage.Classes;
using VAdvantage.Model;
using VAdvantage.Logging;
using System.Data;
using System.Xml.Linq;
using VAdvantage.Utility;
using VAdvantage.DataBase;
using VAdvantage.Login;
using System.Linq;
using System.Text;
using System.Collections.Generic;
using System.Data.SqlClient;


namespace VAdvantage.Grids
{
    /// <summary>
    /// create Lookup field
    /// </summary>
    public class MLookupFactoryCache
    {

       

        /*Table IsTranslated Cache */
        private static CCache<string, bool> _sIsTranslated = new CCache<string, bool>("AD_Table_isTranslated", 10);
        /** Table Reference Cache				*/
        private static CCache<string, MLookupInfo> _sCacheRefTable = new CCache<string, MLookupInfo>("AD_Ref_Table", 30, 60);	//	1h
        /**	Logging								*/
        private static VLogger s_log = VLogger.GetVLogger(typeof(MLookupInfo).FullName);

        
        /// <summary>
        /// Get Information for Lookups based on Column_ID for Table Columns or Process Parameters.
        ///
        ///	The SQL returns three columns:
        /// <pre>
        ///Key, Value, Name, IsActive	(where either key or value is null)
        /// </pre>
        /// </summary>
        /// <param name="lookup">ctx context for access</param>
        /// <param name="Column_ID">AD_Column_ID or AD_Process_Para_ID</param>
        /// <param name="language">report lang</param>
        /// <param name="ColumnName">key column name</param>
        /// <param name="AD_Reference_Value_ID"></param>
        /// <param name="IsParent">parent (prevents query to directly access value)</param>
        /// <param name="ValidationCode">ValidationCode optional SQL validation</param>
        /// <returns>lookup info structure</returns>
        public static MLookupInfo GetLookUpInfo(Lookup lookup,
            int Column_ID, Language language, String columnName, int AD_Reference_Value_ID,
            bool IsParent, String ValidationCode,String SL)
        {
            return GetLookUpInfo(lookup.GetCtx(), lookup.GetWindowNo(), lookup.GetDisplayType(),
                Column_ID, language, columnName, AD_Reference_Value_ID,
                IsParent, ValidationCode,SL);
        }

         



        /// <summary>
        /// Get Information for Lookups based on Column_ID for Table Columns or Process Parameters.
        /// </summary>
        /// <param name="ctx">context</param>
        /// <param name="AD_Reference_ID">ref id</param>
        /// <param name="Column_ID">col id</param>
        /// <param name="ColumnName">name of col</param>
        /// <param name="AD_Reference_Value_ID">ref_val id</param>
        /// <param name="IsParent">is parent col</param>
        /// <param name="ValidationCode">validate text</param>
        /// <returns></returns>
        public static MLookupInfo GetLookUpInfo(Ctx ctx, int windowNum, int AD_Reference_ID,
            int Column_ID, Language language, string columnName, int AD_Reference_Value_ID, bool IsParent, string ValidationCode,String SL)
        {

            MLookupInfo info = null;
            bool needToAddSecurity = true;
            //	List
            if (AD_Reference_ID == DisplayType.List)	//	17
            {
                info = GetLookUp_List(language, AD_Reference_Value_ID);
                needToAddSecurity = false;
            }
            // TAble OR Search with Reference value
            else if ((AD_Reference_ID == DisplayType.Table || AD_Reference_ID == DisplayType.Search)
            && AD_Reference_Value_ID != 0)
            {
                info = GetLookup_Table(ctx, language, windowNum, AD_Reference_Value_ID,SL);
            }
            //	Acct
            else if (AD_Reference_ID == DisplayType.Account)
            {
                info = GetLookup_Acct(ctx, Column_ID);
            }
            //	TableDir, Search, ID, ...
            else
            {
                info = GetLookUp_TableDir(ctx, language, windowNum, columnName,SL);
            }

            if (info == null)
            {
                //s_log..Severe("No SQL - " + columnName);
                return null;
            }
            //	remaining values
            info.column_ID = Column_ID;
            info.windowNo = windowNum;
            info.AD_Reference_Value_ID = AD_Reference_Value_ID;
            info.isParent = IsParent;
            info.validationCode = ValidationCode;

            //variable in sql where 
            if (info.query.IndexOf("@") != -1)
            {
                string newSQL = Utility.Envs.ParseContext(ctx, windowNum, info.query, false);	//	only global
                if (newSQL.Length == 0)
                {
                    //s_log..Severe("SQL parse error: " + info.query);
                    return null;
                }
                info.query = newSQL;
                info.queryAll = VAdvantage.Model.MRole.GetDefault((Context)ctx, false).AddAccessSQL(newSQL,
                   info.tableName, VAdvantage.Model.MRole.SQL_FULLYQUALIFIED, VAdvantage.Model.MRole.SQL_RO);

                //s_log..Fine("SQL =" + newSQL); //jz
            }
            info.queryAll = VAdvantage.Model.MRole.GetDefault((Context)ctx, false).AddAccessSQL(info.query,
                   info.tableName, VAdvantage.Model.MRole.SQL_FULLYQUALIFIED, VAdvantage.Model.MRole.SQL_RO);
            //	Direct Query - NO Validation/Security
            int posOrder = info.query.LastIndexOf(" ORDER BY ");
            bool hasWhere = info.query.LastIndexOf(" WHERE ") != -1;
            if (hasWhere)	//	might be for a select sub-query
            {
                //	SELECT (SELECT .. FROM .. WHERE ..) FROM ..
                //	SELECT .. FROM .. WHERE EXISTS (SELECT .. FROM .. WHERE ..)
                AccessSqlParser asp = new AccessSqlParser(info.query);
                string mainQuery = asp.GetMainSql();
                hasWhere = mainQuery.IndexOf(" WHERE ") != -1;
            }


            if (posOrder == -1)
                info.queryDirect = info.query
                    + (hasWhere ? " AND " : " WHERE ") + info.keyColumn + "=@key";
            else
                info.queryDirect = info.query.Substring(0, posOrder)
                    + (hasWhere ? " AND " : " WHERE ") + info.keyColumn + "=@key";

            //	Validation
            string local_validationCode = "";
            if (info.validationCode == null || info.validationCode.Length == 0) { info.isValidated = true; }
            else
            {
                local_validationCode = Utility.Envs.ParseContext(ctx, windowNum, info.validationCode, true);
                //  returns "" if not all variables were parsed
                if (local_validationCode.Length == 0
                    || info.validationCode.IndexOf("@AD_Org_ID@") != -1)	//	don't validate Org
                {
                    info.isValidated = false;
                    local_validationCode = "";
                }
                else
                    info.isValidated = true;
            }

            //	Add Local Validation
            if (local_validationCode.Length != 0)
            {
                //jz handle no posOrder case
                if (posOrder > 0)
                    info.query = info.query.Substring(0, posOrder)
                        + (hasWhere ? " AND " : " WHERE ") + local_validationCode
                        + info.query.Substring(posOrder);
                else
                    info.query = info.query
                    + (hasWhere ? " AND " : " WHERE ") + local_validationCode;
            }
            //	Add Security
            if (needToAddSecurity)
                info.query = VAdvantage.Model.MRole.GetDefault((Context)ctx, false).AddAccessSQL(info.query,
                    info.tableName, VAdvantage.Model.MRole.SQL_FULLYQUALIFIED, VAdvantage.Model.MRole.SQL_RO);

            return info;
        }

        /// <summary>
        ///Get Lookup SQL for Lists
        /// </summary>
        /// <param name="AD_Reference_Value_ID">ref_val id</param>
        /// <returns></returns>
        public static MLookupInfo GetLookUp_List(Language language, int AD_Reference_Value_ID)
        {
            StringBuilder realSQL = new StringBuilder("SELECT NULL, AD_Ref_List.Value,");
            if (Utility.Envs.IsBaseLanguage(language, "AD_Ref_List"))
            {
                realSQL.Append("AD_Ref_List.Name,AD_Ref_List.IsActive FROM AD_Ref_List");
            }
            else
                realSQL.Append("trl.Name, AD_Ref_List.IsActive "
                    + "FROM AD_Ref_List INNER JOIN AD_Ref_List_Trl trl "
                    + " ON (AD_Ref_List.AD_Ref_List_ID=trl.AD_Ref_List_ID AND trl.AD_Language='")
                        .Append(language.GetAD_Language()).Append("')");
            realSQL.Append(" WHERE AD_Ref_List.AD_Reference_ID=").Append(AD_Reference_Value_ID);
            realSQL.Append(" ORDER BY 2");
            //
            return new MLookupInfo(realSQL.ToString(), "AD_Ref_List", "AD_Ref_List.Value",
                101, 101, Query.GetEqualQuery("AD_Reference_ID", AD_Reference_Value_ID));	//	Zoom Window+Query
        }

        /// <summary>
        ///Get Lookup SQL for List
        /// </summary>
        /// <param name="language"></param>
        /// <param name="AD_Reference_Value_ID"></param>
        /// <param name="linkColumnName"></param>
        /// <returns>SELECT Name FROM AD_Ref_List WHERE AD_Reference_ID=x AND Value=linkColumn</returns>
        public static String GetLookup_ListEmbed(Language language,
            int AD_Reference_Value_ID, String linkColumnName)
        {
            StringBuilder realSQL = new StringBuilder("SELECT ");
            if (Utility.Envs.IsBaseLanguage(language, "AD_Ref_List"))
                realSQL.Append("AD_Ref_List.Name FROM AD_Ref_List");
            else
                realSQL.Append("trl.Name "
                    + "FROM AD_Ref_List INNER JOIN AD_Ref_List_Trl trl "
                    + " ON (AD_Ref_List.AD_Ref_List_ID=trl.AD_Ref_List_ID AND trl.AD_Language='")
                        .Append(language.GetAD_Language()).Append("')");
            realSQL.Append(" WHERE AD_Ref_List.AD_Reference_ID=").Append(AD_Reference_Value_ID)
                .Append(" AND AD_Ref_List.Value=").Append(linkColumnName);
            //
            return realSQL.ToString();
        }


        /// <summary>
        ///	Get Lookup SQL for direct Table Lookup
        /// </summary>
        /// <param name="ColumnName">column name</param>
        /// <returns></returns>
        public static MLookupInfo GetLookUp_TableDir(Ctx ctx, Language language, int winNum, string columnName,String SL)
        {
            if (!columnName.ToUpper().EndsWith("_ID"))
            {
                //Common.ErrorLog.FillErrorLog("VLookupInfo", "", "Key does not end with '_ID': " + ColumnName, VAdvantage.Framework.Message.MessageType.ERROR);
                //s_log..Log(Level.SEVERE, "Key does not end with '_ID': " + columnName);
                return null;
            }
            //	Hardcoded BPartner Org
            if (columnName.Equals("AD_OrgBP_ID"))
                columnName = "AD_Org_ID";

            if (columnName.IndexOf("M_Locator") != -1)
                columnName = "M_Locator_ID";

            string tableName = columnName.Substring(0, columnName.Length - 3);
            //	boolean isSOTrx = !"N".Equals(ctx.getContext( WindowNo, "IsSOTrx"));
            int ZoomWindow = 0;
            int ZoomWindowPO = 0;
            bool isTranslated = false;
            string keyColumn = columnName;

            string sql = "SELECT t.AD_Window_ID,t.PO_Window_ID "
                + "FROM AD_Table t "
                + "WHERE tableName=@tableName ";

            //var 


            //IDataReader dr = null;
            try
            {
                var dr = from t in DataStorage.GetLookupInfoCache().Descendants("Table0")
                         where Util.GetValueofString(t, "TableName") == tableName
                         select t;
                         

                


                //SqlParameter[] param = new SqlParameter[1];
                //param[0] = new SqlParameter("@tableName", tableName);

                //dr = DB.ExecuteReader(sql, param);
                //while (dr.Read())
                //{
                //    ZoomWindow = Utility.Util.GetValueOfInt(dr[0]);
                //    ZoomWindowPO = Utility.Util.GetValueOfInt(dr[1]);
                //}

            foreach(var item in dr)
            {
                ZoomWindow = Utility.Util.GetValueofInt(item,"AD_Window_ID");
                ZoomWindowPO = Utility.Util.GetValueofInt(item,"PO_Window_ID");
            }

                //dr.Close();
                //dr = null;
                //param = null;

            }
            catch (Exception e)
            {
                //if (dr != null)
                //{
                //    dr.Close();
                //    dr = null;
                //}
                //s_log..Log(Level.SEVERE, sql, e);
                return null;
            }
          
            isTranslated = IsTranslated(tableName,SL);

            StringBuilder realSQL = new StringBuilder("SELECT ");
            realSQL.Append(tableName).Append(".").Append(keyColumn).Append(",NULL,");

            StringBuilder displayColumn = GetLookup_DisplayColumn(language, tableName,SL);

            realSQL.Append((displayColumn == null) ? "NULL" : displayColumn.ToString());
            realSQL.Append(",").Append(tableName).Append(".IsActive");

            //  Translation
            if (isTranslated && !Envs.IsBaseLanguage(language, tableName))//  GlobalVariable.IsBaseLanguage())
            {
                realSQL.Append(" FROM ").Append(tableName)
                    .Append(" INNER JOIN ").Append(tableName).Append("_TRL ON (")
                    .Append(tableName).Append(".").Append(keyColumn)
                    .Append("=").Append(tableName).Append("_Trl.").Append(keyColumn)
                    .Append(" AND ").Append(tableName).Append("_Trl.AD_Language='")
                    .Append(language.GetAD_Language()).Append("')");
            }
            else	//	no translation
            {
                realSQL.Append(" FROM ").Append(tableName);
            }

            //	Order by Display    
            realSQL.Append(" ORDER BY 3");
            //	((LookupDisplayColumn)list.get(3)).ColumnName);
            Query zoomQuery = null;	//	corrected in VLookup

            //if (VLogMgt.IsLevelFinest())
            //s_log..Fine("ColumnName=" + columnName + " - " + realSQL);
            MLookupInfo lInfo = new MLookupInfo(realSQL.ToString(), tableName,
                tableName + "." + keyColumn, ZoomWindow, ZoomWindowPO, zoomQuery);
            return lInfo;
        }


        /// <summary>
        ///Get embedded SQL for TableDir Lookup (no translation)
        /// </summary>
        /// <param name="language">language</param>
        /// <param name="ColumnName">col name</param>
        /// <param name="BaseTable">base table name</param>
        /// <returns>ELECT Column FROM TableName WHERE BaseTable.ColumnName=TableName.ColumnName</returns>
        static public String GetLookup_TableDirEmbed(Language language, String columnName, String baseTable,String SL)
        {
            return GetLookup_TableDirEmbed(language, columnName, baseTable, columnName,SL);
        }

        /// <summary>
        /// Get embedded SQL for TableDir Lookup (no translation)
        /// </summary>
        /// <param name="language"></param>
        /// <param name="ColumnName"></param>
        /// <param name="BaseTable"></param>
        /// <param name="BaseColumn"></param>
        /// <returns>SELECT Column FROM TableName WHERE BaseTable.BaseColumn=TableName.ColumnName</returns>
        public static String GetLookup_TableDirEmbed(Language language,
            String columnName, String baseTable, String baseColumn,String SL)
        {
            String tableName = columnName.Substring(0, columnName.Length - 3);

            //	get display column name (first identifier column)
            String sql = "SELECT c.ColumnName,c.IsTranslated,c.AD_Reference_ID,c.AD_Reference_Value_ID "
                + "FROM AD_Table t INNER JOIN AD_Column c ON (t.AD_Table_ID=c.AD_Table_ID) "
                + "WHERE TableName='" + tableName + "'"
                + " AND c.IsIdentifier='Y' "
                + "ORDER BY c.SeqNo";
            //
            List<LookupDisplayColumn> list = new List<LookupDisplayColumn>();
            //
            //IDataReader dr = null;
            try
            {
                //var dr = from t in DataStorage.GetLookupDiplayCol().Descendants("Table")
                //         where Util.GetValueofString(t, "TABLENAME") == tableName
                //         select t;
                var dr = from t in DataStorage.GetLookupInfoCache().Descendants("Table1")
                         where Util.GetValueofString(t, "TABLENAME") == tableName
                         select t;

                foreach (var item in dr)
                {
                    LookupDisplayColumn ldc = new LookupDisplayColumn(Util.GetValueofString(item,"ColumnName"),
                        "Y".Equals(Util.GetValueofString(item,"IsTranslated")), Utility.Util.GetValueofInt(item,"AD_Reference_ID"),
                        Utility.Util.GetValueofInt(item,"AD_Reference_Value_ID"));
                    list.Add(ldc);

                }



                //PreparedStatement pstmt = DB.prepareStatement(sql, null);
               // dr = DataBase.DB.ExecuteReader(sql);
                //while (dr.Read())
                //{
                //    LookupDisplayColumn ldc = new LookupDisplayColumn(dr[0].ToString(),
                //        "Y".Equals(dr[1].ToString()), Utility.Util.GetValueOfInt(dr[2]), Utility.Util.GetValueOfInt(dr[3]));
                //    list.Add(ldc);
                //    //	//s_log..fine("getLookup_TableDirEmbed: " + ColumnName + " - " + ldc);
                //}
                //dr.Close();
                //dr = null;
            }
            catch (System.Exception e)
            {
                //if (dr != null)
                //{
                //    dr.Close();
                //    dr = null;
                //}
                ////Common.ErrorLog.FillErrorLog("VlookupFactory", sql, e.Message , VAdvantage.Framework.Message.MessageType.ERROR);
                //s_log..Log(Level.SEVERE, sql, e);
                return "";
            }
            //  Do we have columns ?
            if (list.Count == 0)
            {
                //Common.ErrorLog.FillErrorLog("VlookupFactory", "", "No Identifier records found: " + columnName, VAdvantage.Framework.Message.MessageType.ERROR);
                //s_log..Log(Level.SEVERE, "No Identifier records found: " + columnName);
                return "";
            }

            //
            StringBuilder embedSQL = new StringBuilder("SELECT ");

            int size = list.Count;
            for (int i = 0; i < size; i++)
            {
                if (i > 0)
                    embedSQL.Append("||' - '||");
                LookupDisplayColumn ldc = list[i];

                //  date, number
                if (DisplayType.IsDate(ldc.DisplayType) || DisplayType.IsNumeric(ldc.DisplayType))
                {
                    embedSQL.Append(DB.TO_CHAR(tableName + "." + ldc.ColumnName, ldc.DisplayType, language.GetAD_Language()));
                }
                //  TableDir
                else if ((ldc.DisplayType == DisplayType.TableDir || ldc.DisplayType == DisplayType.Search)
                  && ldc.ColumnName.EndsWith("_ID"))
                {
                    String embeddedSQL = GetLookup_TableDirEmbed(language, ldc.ColumnName, tableName,SL);
                    embedSQL.Append("(").Append(embeddedSQL).Append(")");
                }
                //  String
                else
                {
                    //jz EDB || problem
                    if (DatabaseType.IsPostgre)
                        embedSQL.Append("COALESCE(TO_CHAR(").Append(tableName).Append(".").Append(ldc.ColumnName).Append("),'')");
                    else
                        embedSQL.Append(tableName).Append(".").Append(ldc.ColumnName);
                }
            }

            embedSQL.Append(" FROM ").Append(tableName);
            embedSQL.Append(" WHERE ").Append(baseTable).Append(".").Append(baseColumn);
            embedSQL.Append("=").Append(tableName).Append(".").Append(columnName);
            //
            return embedSQL.ToString();
        }


        /// <summary>
        /// Check Table Is Translatation type or not
        /// </summary>
        /// <param name="tableName">table name</param>
        /// <returns>true if translated</returns>
        private static bool IsTranslated(string tableName,String SL)
        {
            bool isTranslated = false;

            if (_sIsTranslated.ContainsKey(tableName))
            {
                return _sIsTranslated[tableName];
            }

            string sql1 = "SELECT count(*) "
                + "FROM AD_Table t"
                + " INNER JOIN AD_Column c ON (t.AD_Table_ID=c.AD_Table_ID) "
                + "WHERE tableName=@tableName"
                + " AND c.IsIdentifier='Y' "
                + " AND c.IsTranslated = 'Y' ";

            try
            {
                //var count = from t in DataStorage.GetLookupTranslated().Descendants("Table")
                //            where Util.GetValueofString(t, "TABLENAME") == tableName
                //            select t;

                var count = from t in DataStorage.GetLookupInfoCache().Descendants("Table3")
                            where Util.GetValueofString(t, "TABLENAME") == tableName
                            select t;
                     
                //SqlParameter[] param = new SqlParameter[1];
                //param[0] = new SqlParameter("@tableName", tableName);
                isTranslated = false;
                if (count.Count() > 0)
                {
                    isTranslated = true;
                }

                //int count = Utility.Util.GetValueOfInt(DB.ExecuteScalar(sql1, param, null));
              //  isTranslated = !(count == 0);
                //param = null;
            }
            catch (Exception e)
            {
                //s_log..Log(Level.SEVERE, sql1, e);
            }
            if (!_sCacheRefTable.ContainsKey(tableName))
            {
                _sIsTranslated.Add(tableName, isTranslated);
            }
            return isTranslated;
        }

        /// <summary>
        /// Get Display Columns SQL for Table/Table Direct Lookup
        /// </summary>
        /// <param name="tableName">table name</param>
        /// <param name="language">language object </param>
        /// <returns></returns>
        public static StringBuilder GetLookup_DisplayColumn(Language language, string tableName,String SL)
        {

            //	get display column names
            String sql0 = "SELECT c.ColumnName,c.IsTranslated,c.AD_Reference_ID,"
                + "c.AD_Reference_Value_ID,t.AD_Window_ID,t.PO_Window_ID "
                + "FROM AD_Table t"
                + " INNER JOIN AD_Column c ON (t.AD_Table_ID=c.AD_Table_ID) "
                + "WHERE tableName=@tableName"
                + " AND c.IsIdentifier='Y' "
                + "ORDER BY c.SeqNo";

            //var dr = from t in DataStorage.GetLookupDiplayCol().Descendants("Table")
            //         where Util.GetValueofString(t,"TableName") == tableName
            //         select t;
            var dr = from t in DataStorage.GetLookupInfoCache().Descendants("Table1")
                     where Util.GetValueofString(t, "TableName") == tableName
                     select t;
                      //{
                      //    ColumnName = c["COLUMNNAME"].ToString(),
                      //    IsTranslated = c["ISTRANSLATED"].ToString(),
                      //    AD_Rererence_ID = c["AD_REFERENCE_ID"].ToString(),
                      //    AD_Reference_Value_ID = c["AD_REFERENCE_VALUE_ID"]
                      //   // AD_Window_ID = t["AD_WINDOW_ID").Value,
                      //    //PO_Window_ID = t["PO_WINDOW_ID").Value
                      //};


            List<LookupDisplayColumn> list = new List<LookupDisplayColumn>();
            bool isTranslated = false;
            //IDataReader dr = null;
            try
            {
                foreach (var itm in dr)
                {

                    LookupDisplayColumn ldc = new LookupDisplayColumn(Util.GetValueofString(itm,"ColumnName"),
                        "Y".Equals(Util.GetValueofString(itm, "IsTranslated")), Utility.Util.GetValueofInt(itm, "AD_Reference_ID"),
                      Utility.Util.GetValueofInt(itm, "AD_Reference_Value_ID"));
                    list.Add(ldc);

                    if (!isTranslated && ldc.IsTranslated)
                        isTranslated = true;

                }

                //SqlParameter[] param = new SqlParameter[1];
                //param[0] = new SqlParameter("@tableName", tableName);
               // dr = DB.ExecuteReader(sql0, param);
                //while (dr.Read())
                //{
                //    LookupDisplayColumn ldc = new LookupDisplayColumn(dr[0].ToString(),
                //        "Y".Equals(dr[1].ToString()), Utility.Util.GetValueOfInt(dr[2]), Utility.Util.GetValueOfInt(dr[3]));
                //    list.Add(ldc);

                //    if (!isTranslated && ldc.IsTranslated)
                //        isTranslated = true;
                //}
                //dr.Close();
                //dr = null;
                //param = null;
            }
            catch (Exception e)
            {
                //if (dr != null)
                //{
                //    //dr.Close();
                //    dr = null;
                //}
                //s_log..Log(Level.SEVERE, sql0, e);
                return null;
            }

            //  Do we have columns ?
            if (list.Count == 0)
            {
                //s_log..Log(Level.SEVERE, "No Identifier records found: " + tableName);
                //Common.ErrorLog.FillErrorLog("VlookupFactory", "", "No Identifier records found: " + tableName, VAdvantage.Framework.Message.MessageType.ERROR);
                return null;
            }

            StringBuilder displayColumn = new StringBuilder("");
            int size = list.Count;
            //  Get Display Column
            for (int i = 0; i < size; i++)
            {
                if (i > 0)
                    displayColumn.Append(" ||'_'|| ");
                LookupDisplayColumn ldc = list[i];
                //jz EDB || problem
                if (DatabaseType.IsPostgre)
                    displayColumn.Append("COALESCE(TO_CHAR(");
                else if (DatabaseType.IsMSSql)
                    displayColumn.Append("COALESCE(CONVERT(VARCHAR,");
                //  translated
                if (ldc.IsTranslated && !Envs.IsBaseLanguage(language, tableName))//  DataBase.GlobalVariable.IsBaseLanguage())
                    displayColumn.Append(tableName).Append("_Trl.").Append(ldc.ColumnName);
                //  date
                else if (DisplayType.IsDate(ldc.DisplayType))
                {
                    displayColumn.Append(DB.TO_CHAR(tableName + "." + ldc.ColumnName, ldc.DisplayType, language.GetAD_Language()));
                }
                //  TableDir
                else if ((ldc.DisplayType == DisplayType.TableDir || ldc.DisplayType == DisplayType.Search)
                    && ldc.ColumnName.EndsWith("_ID"))
                {
                    string embeddedSQL = GetLookup_TableDirEmbed(language, ldc.ColumnName, tableName,SL);
                    if (embeddedSQL != null)
                        displayColumn.Append("(").Append(embeddedSQL).Append(")");
                }
                //	Table
                else if (ldc.DisplayType == DisplayType.Table && ldc.AD_Ref_Val_ID != 0)
                {
                    string embeddedSQL = GetLookup_TableEmbed(language, ldc.ColumnName, tableName, ldc.AD_Ref_Val_ID,SL);
                    if (embeddedSQL != null)
                        displayColumn.Append("(").Append(embeddedSQL).Append(")");
                }
                //  number
                else if (DisplayType.IsNumeric(ldc.DisplayType))
                {
                    displayColumn.Append(DB.TO_CHAR(tableName + "." + ldc.ColumnName, ldc.DisplayType, language.GetAD_Language()));
                }
                //  String
                else
                {
                    //jz EDB || null issue
                    if (DatabaseType.IsPostgre)
                        displayColumn.Append("COALESCE(TO_CHAR(").Append(tableName).Append(".").Append(ldc.ColumnName).Append("),'')");
                    else if (DatabaseType.IsMSSql)
                        displayColumn.Append("COALESCE(CONVERT(VARCHAR,").Append(tableName).Append(".").Append(ldc.ColumnName).Append("),'')");
                    else
                        displayColumn.Append(tableName).Append(".").Append(ldc.ColumnName);
                }

                //jz EDB || problem
                if (DatabaseType.IsPostgre || DatabaseType.IsMSSql)
                    displayColumn.Append("),'')");
            }
            return displayColumn;
        }

        /// <summary>
        /// Get embedded SQL for TableDir Lookup (no translation)
        /// </summary>
        /// <param name="ColumnName">column name </param>
        /// <param name="BaseTable">table name</param>
        /// <returns></returns>
        public static string GetLookup_TableDirEmbed(string columnName, string baseTable)
        {
            return GetLookup_TableDirEmbed(columnName, baseTable, columnName);
        }

        /// <summary>
        ///Get embedded SQL for TableDir Lookup (no translation)
        /// </summary>
        /// <param name="ColumnName">column name </param>
        /// <param name="BaseTable">base table name</param>
        /// <param name="BaseColumn">base column name0</param>
        /// <returns>SELECT Column FROM tableName WHERE BaseTable.BaseColumn=tableName.ColumnName</returns>
        public static string GetLookup_TableDirEmbed(string columnName, string baseTable, string baseColumn)
        {
            string tableName = columnName.Substring(0, columnName.Length - 3);

            //	get display column name (first identifier column)
            string sql = "SELECT c.ColumnName,c.IsTranslated,c.AD_Reference_ID,c.AD_Reference_Value_ID "
                + "FROM AD_Table t INNER JOIN AD_Column c ON (t.AD_Table_ID=c.AD_Table_ID) "
                + "WHERE tableName='" + tableName + "' "
                + " AND c.IsIdentifier='Y' "
                + "ORDER BY c.SeqNo";
            //
            List<LookupDisplayColumn> list = new List<LookupDisplayColumn>();
            //
            IDataReader dr = null;
            try
            {
                dr = DB.ExecuteReader(sql);
                while (dr.Read())
                {
                    LookupDisplayColumn ldc = new LookupDisplayColumn(Utility.Util.GetValueOfString(dr[0]),
                        "Y".Equals(Utility.Util.GetValueOfString(dr[1])), Utility.Util.GetValueOfInt(dr[2]), Utility.Util.GetValueOfInt(dr[3]));
                    list.Insert(list.Count, ldc);
                }
                dr.Close();
                dr = null;
            }
            catch (Exception e)
            {
                if (dr != null)
                {
                    dr.Close();
                    dr = null;
                }
                //log error
                //s_log..Log(Logging.Level.SEVERE, e.Message);
                return "";
            }

            //  Do we have columns ?
            if (list.Count == 0)
            {
                //s_log..Log(Level.SEVERE, "No Identifier records found: " + columnName);
                return "";
            }
            //
            StringBuilder embedSQL = new StringBuilder("SELECT ");

            int size = list.Count;
            for (int i = 0; i < size; i++)
            {
                if (i > 0)
                    embedSQL.Append("||' - '||");
                LookupDisplayColumn ldc = list[i];

                //  date, number
                if (DisplayType.IsDate(ldc.DisplayType) || DisplayType.IsNumeric(ldc.DisplayType))
                {
                    embedSQL.Append(DB.TO_CHAR(tableName + "." + ldc.ColumnName, ldc.DisplayType, Language.GetLoginLanguage().GetName()));
                }
                //  TableDir
                else if ((ldc.DisplayType == DisplayType.TableDir || ldc.DisplayType == DisplayType.Search)
                  && ldc.ColumnName.EndsWith("_ID"))
                {
                    string embeddedSQL = GetLookup_TableDirEmbed(ldc.ColumnName, tableName);
                    embedSQL.Append("(").Append(embeddedSQL).Append(")");
                }
                //  String
                else
                {
                    //jz EDB || problem
                    if (DatabaseType.IsPostgre)
                        embedSQL.Append("COALESCE(TO_CHAR(").Append(tableName).Append(".").Append(ldc.ColumnName).Append("),'')");
                    else
                        embedSQL.Append(tableName).Append(".").Append(ldc.ColumnName);
                }
            }

            embedSQL.Append(" FROM ").Append(tableName);
            embedSQL.Append(" WHERE ").Append(baseTable).Append(".").Append(baseColumn);
            embedSQL.Append("=").Append(tableName).Append(".").Append(columnName);
            //
            return embedSQL.ToString();
        }	//  getLookup_Table

        /// <summary>
        /// Get Lookup SQL for Table Lookup
        /// </summary>
        /// <param name="AD_Reference_Value_ID"></param>
        /// <returns></returns>
        private static MLookupInfo GetLookup_Table(Ctx ctx, Language language, int windowNum, int AD_Reference_Value_ID,String  SL)
        {
            string key = AD_Reference_Value_ID.ToString();
            MLookupInfo retValue = null;
            _sCacheRefTable.TryGetValue(key, out retValue);
            if (retValue != null)
            {
                //s_log..Finest("Cache: " + retValue);
                return retValue.Clone();
            }

            //string sql0 = "SELECT t.TableName,ck.ColumnName AS KeyColumn,"				//	1..2
            //+ "cd.ColumnName AS DisplayColumn,rt.IsValueDisplayed,cd.IsTranslated,"	//	3..5
            //+ "rt.WhereClause,rt.OrderByClause,t.AD_Window_ID,t.PO_Window_ID, "		//	6..9
            //+ "t.AD_Table_ID " // rt.IsDisplayIdentifiers "								//	10..11
            //+ "FROM AD_Ref_Table rt"
            //+ " INNER JOIN AD_Table t ON (rt.AD_Table_ID=t.AD_Table_ID)"
            //+ " INNER JOIN AD_Column ck ON (rt.Column_Key_ID=ck.AD_Column_ID)"
            //+ " INNER JOIN AD_Column cd ON (rt.Column_Display_ID=cd.AD_Column_ID) "
            //+ "WHERE rt.AD_Reference_ID = '" + key + "' "
            //+ " AND rt.IsActive='Y' AND t.IsActive='Y'";
            //
            string keyColumn = "", tableName = "", whereClause = "", orderByClause = "";
            // string displayColumn = "";
            bool isTranslated = false, isValueDisplayed = false, isDisplayIdentifiers = false;
            int zoomWindow = 0;
            int zoomWindowPO = 0;
            //	int AD_Table_ID = 0;
            bool loaded = false;
            
           // IDataReader dr = null;
            try
            {
               // dr = DB.ExecuteReader(sql0);

                //var dr = from rt in DataStorage.GetRefTable().Rows
                //         join t in DataStorage.GetTable().Rows
                //          on
                //              rt["AD_TABLE_ID"]
                //            equals
                //               t["AD_TABLE_ID"]
                //          join
                //          ck in DataStorage.GetColumn().Rows
                //          on
                //                 rt["COLUMN_KEY_ID"]
                //            equals
                //                 ck["AD_COLUMN_ID"]

                //          join
                //          cd in DataStorage.GetColumn().Rows

                //          on
                //               rt["COLUMN_DISPLAY_ID"]
                //           equals
                //          cd["AD_COLUMN_ID"]

                //          where rt["AD_REFERENCE_ID"].ToString() == key.ToString() 
                //          && t["ISACTIVE"].ToString() == "Y"

                //          select new
                //{
                //    TableName = t["TABLENAME"].ToString(),
                //    IsValueDisplayed = rt["ISVALUEDISPLAYED"].ToString(),
                //    WhereClause =  rt["WHERECLAUSE"],
                //    OrderByClause =rt["ORDERBYCLAUSE"],
                //    KeyColumn = ck["COLUMNNAME"].ToString(),
                //    //DisplayColumn = cd["COLUMNNAME"].ToString(),
                //    PO_Window_ID = t["PO_WINDOW_ID"],
                //    IsTranslated = cd["ISTRANSLATED"].ToString(),
                //    AD_Window_ID = t["AD_WINDOW_ID"]
                //};

                //var dr = from rt in DataStorage.GetLookupTable().Descendants("Table")
                //           where Util.GetValueofString(rt,"AD_Reference_ID")  == AD_Reference_Value_ID.ToString()
                //           select rt;
                var dr = from rt in DataStorage.GetLookupInfoCache().Descendants("Table2")
                           where Util.GetValueofString(rt, "AD_Reference_ID") == AD_Reference_Value_ID.ToString()
                           select rt;

                bool success = false;
               // foreach (var itm in data)
                foreach (var item in dr)
                {
                    tableName = Util.GetValueofString(item,"TableName");
                    keyColumn = Util.GetValueofString(item, "KeyColumn"); ;
                       ;
                    //displayColumn = dr[2].ToString();
                       isValueDisplayed = "Y".Equals(Util.GetValueofString(item, "IsValueDisplayed"));
                    isTranslated = "Y".Equals(Util.GetValueofString(item,"IsTranslated"));
                    whereClause = Util.GetValueofString(item, "WhereClause"); 
                    
                        //whereClause = "";

                    orderByClause = Util.GetValueofString(item, "OrderByClause") ;// == null ?"" :item.OrderByClause.ToString();

                    zoomWindow = Util.GetValueofInt(item, "AD_Window_ID");
                    zoomWindowPO = Util.GetValueofInt(item, "PO_Window_ID"); 
                    loaded = true;
                }

                dr = null;
                //while (dr.Read())
                //{
                //    tableName = dr[0].ToString();
                //    keyColumn = dr[1].ToString();
                //    //displayColumn = dr[2].ToString();
                //    isValueDisplayed = "Y".Equals(dr[3].ToString());
                //    isTranslated = "Y".Equals(dr[4].ToString());
                //    whereClause = dr[5].ToString();
                //    orderByClause = dr[6].ToString();
                //    zoomWindow = Utility.Util.GetValueOfInt(dr[7]);
                //    zoomWindowPO = Utility.Util.GetValueOfInt(dr[8]);
                //    loaded = true;
                //}
                //dr.Close();
                //dr = null;

            }
            catch (Exception e)
            {
                //if (dr != null)
                //{
                //    //dr.Close();
                //    dr = null;
                //}
                //s_log..Log(Level.SEVERE, sql0, e);
                return null;
            }

            if (!loaded)
            {
                //s_log..Log(Level.SEVERE, "No Table Reference Table ID=" + AD_Reference_Value_ID);
                return null;
            }

            // if (isDisplayIdentifiers)
            StringBuilder displayColumn = GetLookup_DisplayColumn(language, tableName,SL);
            if (displayColumn == null)
            {
                displayColumn = new StringBuilder("NULL");
            }
            // else
            //   displayColumn = tableName + "." + displayColumn;

            StringBuilder sb = new StringBuilder("SELECT ");
            if (!keyColumn.EndsWith("_ID")) { sb.Append("NULL,"); }

            isTranslated = IsTranslated(tableName,SL);
            //Translated
            if (isTranslated && !Envs.IsBaseLanguage(language, tableName))//  GlobalVariable.IsBaseLanguage())
            {
                sb.Append(tableName).Append(".").Append(keyColumn).Append(",");
                if (keyColumn.EndsWith("_ID"))
                    sb.Append("NULL,");
                if (isValueDisplayed)
                    sb.Append(tableName).Append(".Value || '-' || ");

                sb.Append(displayColumn.ToString());

                sb.Append(",").Append(tableName).Append(".IsActive");
                sb.Append(" FROM ").Append(tableName)
                    .Append(" INNER JOIN ").Append(tableName).Append("_TRL ON (")
                    .Append(tableName).Append(".").Append(keyColumn)
                    .Append("=").Append(tableName).Append("_Trl.").Append(keyColumn)
                    .Append(" AND ").Append(tableName).Append("_Trl.AD_Language='")
                    .Append(language.GetAD_Language()).Append("')");
            }
            //	Not Translated
            else
            {
                sb.Append(tableName).Append(".").Append(keyColumn).Append(",");
                if (keyColumn.EndsWith("_ID"))
                    sb.Append("NULL,");
                if (isValueDisplayed)
                    sb.Append(tableName).Append(".Value || '-' || ");
                //jz EDB || problem
                if (DatabaseType.IsPostgre)
                    sb.Append("COALESCE(TO_CHAR(").Append(displayColumn).Append("),'')");
                else if (DatabaseType.IsMSSql)
                    sb.Append("COALESCE(CONVERT(VARCHAR,").Append(displayColumn).Append("),'')");
                else
                    sb.Append(displayColumn);
                sb.Append(",").Append(tableName).Append(".IsActive");
                sb.Append(" FROM ").Append(tableName);
            }
            //if (!isDisplayIdentifiers)
            //    sb.Append(" WHERE " + displayColumn + " IS NOT NULL ");

            //	add WHERE clause
            Query zoomQuery = null;
            if (whereClause != "")
            {
                string where = whereClause;
                if (where.IndexOf("@") != -1)
                    where = Utility.Envs.ParseContext(ctx, windowNum, where, false);
                if (where.Length == 0 && whereClause.Length != 0)
                {
                    //s_log..Severe("Could not resolve: " + whereClause);
                    //Common.ErrorLog.FillErrorLog("VlookupFactory","","Could not resolve: " + whereClause,VAdvantage.Framework.Message.MessageType.ERROR);
                }


                //	We have no context
                if (where.Length != 0)
                {
                    sb.Append(" WHERE ").Append(where);
                    if (where.IndexOf(".") == -1)
                    {
                        //s_log..Log(Level.SEVERE, "Table - " + tableName
                        //+ ": WHERE should be fully qualified: " + whereClause);
                    }
                    zoomQuery = new Query(tableName);
                    zoomQuery.AddRestriction(where);
                }
            }

            //	Order By qualified term or by Name
            if (orderByClause != "")
            {
                sb.Append(" ORDER BY ").Append(orderByClause);
                if (orderByClause.IndexOf(".") == -1)
                {
                    //s_log..Log(Level.SEVERE, "getLookup_Table - " + tableName
                    //   + ": ORDER BY must fully qualified: " + orderByClause);
                }
            }
            else
                sb.Append(" ORDER BY 3");

            //s_log..Finest("AD_Reference_Value_ID=" + AD_Reference_Value_ID + " - " + sb.ToString());
            retValue = new MLookupInfo(sb.ToString(), tableName,
                tableName + "." + keyColumn, zoomWindow, zoomWindowPO, zoomQuery);
            //if(!_sCacheRefTable.ContainsKey(key))
            // {
            _sCacheRefTable[key] = retValue.Clone();
            // }
            return retValue;
        }

        /// <summary>
        ///Get Embedded Lookup SQL for Table Lookup
        /// </summary>
        /// <param name="BaseColumn"></param>
        /// <param name="BaseTable"></param>
        /// <param name="AD_Reference_Value_ID"></param>
        /// <returns></returns>
        static public string GetLookup_TableEmbed(Language language, string BaseColumn, string BaseTable, int AD_Reference_Value_ID,String SL)
        {
            //string sql = "SELECT t.tableName,ck.ColumnName AS keyColumn,"
            //    + "cd.ColumnName AS DisplayColumn,rt.IsValueDisplayed,cd.IsTranslated "
            //    + "FROM AD_Ref_Table rt"
            //    + " INNER JOIN AD_Table t ON (rt.AD_Table_ID=t.AD_Table_ID)"
            //    + " INNER JOIN AD_Column ck ON (rt.Column_Key_ID=ck.AD_Column_ID)"
            //    + " INNER JOIN AD_Column cd ON (rt.Column_Display_ID=cd.AD_Column_ID) "
            //    + "WHERE rt.AD_Reference_ID=" + AD_Reference_Value_ID.ToString() + ""
            //    + " AND rt.IsActive='Y' AND t.IsActive='Y'";
            //
            string keyColumn = "", DisplayColumn = "", tableName = "";
            bool isTranslated = false, isValueDisplayed = false;
            IDataReader dr = null;
            try
            {
                //var data = from rt in DataStorage.GetRefTable().Rows
                //           join t in DataStorage.GetTable().Rows
                //           on
                //           rt["AD_TABLE_ID"]
                //           equals
                //            t["AD_TABLE_ID"]
                //           join ck in DataStorage.GetColumn().Rows
                //           on
                //           rt["COLUMN_KEY_ID"]
                //           equals
                //           ck["AD_COLUMN_ID"]
                //           join cd in DataStorage.GetColumn().Rows
                //           on
                //             rt["COLUMN_DISPLAY_ID"]
                //             equals
                //           cd["AD_COLUMN_ID"]
                //           where
                //           rt["AD_REFERENCE_ID"].ToString() == AD_Reference_Value_ID.ToString()
                //           && t["ISACTIVE"].ToString() == "Y"
                //           select new
                //           {
                //               TableName = t["TABLENAME"].ToString(),
                //               KeyColumn = ck["COLUMNNAME"].ToString(),
                //               disPlayColumn = cd["COLUMNNAME"].ToString(),
                //               IsValueDisplayed = rt["ISVALUEDISPLAYED"].ToString(),
                //               IsTranslated = cd["ISTRANSLATED"].ToString()
                //           };

                //dr = DB.ExecuteReader(sql);




                //var data = from rt in DataStorage.GetLookupTable().Descendants("Table")
                //           where Util.GetValueofString(rt,"AD_Reference_ID") == AD_Reference_Value_ID.ToString()
                //           select rt;

                var data = from rt in DataStorage.GetLookupInfoCache().Descendants("Table2")
                           where Util.GetValueofString(rt, "AD_Reference_ID") == AD_Reference_Value_ID.ToString()
                           select rt;

                bool success = false;
                foreach (var itm in data)
                {
                    tableName = Util.GetValueofString(itm, "TableName");
                    keyColumn = Util.GetValueofString(itm, "KeyColumn");
                    DisplayColumn = Util.GetValueofString(itm, "disPlayColumn");
                    isValueDisplayed = "Y".Equals(Util.GetValueofString(itm, "IsValueDisplayed"));
                    isTranslated = "Y".Equals(Util.GetValueofString(itm, "IsTranslated"));
                    success = true;
                }


                //while (dr.Read())
                //{
                //    tableName = Utility.Util.GetValueOfString(dr[0]);
                //    keyColumn = Utility.Util.GetValueOfString(dr[1]);
                //    DisplayColumn = Utility.Util.GetValueOfString(dr[2]);
                //    isValueDisplayed = dr[3].ToString().Equals("Y");
                //    isTranslated = dr[4].ToString().Equals("Y");
                //    success = true;
                //}
                //dr.Close();
                //dr = null;
                if (!success)
                {
                    //s_log..Log(Level.SEVERE, "Cannot find Reference Table, ID=" + AD_Reference_Value_ID
                    // + ", Base=" + BaseTable + "." + BaseColumn);
                    return null;
                }
            }
            catch (Exception e)
            {
                if (dr != null)
                {
                    dr.Close();
                    dr = null;
                }
                //s_log..Log(Level.SEVERE, sql, e);
                return null;
            }

            StringBuilder embedSQL = new StringBuilder("SELECT ");
            //	Translated
            if (isTranslated && !Envs.IsBaseLanguage(language, tableName))// GlobalVariable.IsBaseLanguage())
            {
                if (isValueDisplayed)
                    embedSQL.Append(tableName).Append(".Value||'-'||");
                embedSQL.Append(tableName).Append("_Trl.").Append(DisplayColumn);
                //
                embedSQL.Append(" FROM ").Append(tableName)
                    .Append(" INNER JOIN ").Append(tableName).Append("_TRL ON (")
                    .Append(tableName).Append(".").Append(keyColumn)
                    .Append("=").Append(tableName).Append("_Trl.").Append(keyColumn)
                    .Append(" AND ").Append(tableName).Append("_Trl.AD_Language='")
                    .Append(language.GetAD_Language()).Append("')");
            }
            //	Not Translated
            else
            {
                if (isValueDisplayed)
                    embedSQL.Append(tableName).Append(".Value||'-'||");
                embedSQL.Append(tableName).Append(".").Append(DisplayColumn);
                //
                embedSQL.Append(" FROM ").Append(tableName);
            }

            embedSQL.Append(" WHERE ").Append(BaseTable).Append(".").Append(BaseColumn);
            embedSQL.Append("=").Append(tableName).Append(".").Append(keyColumn);

            return embedSQL.ToString();
        }	//	getLookup_TableEmbed

        /// <summary>
        /// crate Lookup_Acct info
        /// </summary>
        /// <param name="AD_Column_ID"></param>
        /// <returns></returns>
        private static MLookupInfo GetLookup_Acct(Ctx ctx, int AD_Column_ID)
        {
            //	Try cache - assume no language change
            string key = "Acct" + AD_Column_ID;
            MLookupInfo retValue = null;
            _sCacheRefTable.TryGetValue(key, out retValue);
            if (retValue != null)
            {
                //s_log..Finest("Cache: " + retValue);
                return retValue.Clone();
            }
            string sql = "SELECT C_ValidCombination_ID, NULL, Combination, IsActive FROM C_ValidCombination";
            int zoomWindow = 153;
            Query zoomQuery = new Query("C_ValidCombination");
            //
            retValue = new MLookupInfo(sql, "C_ValidCombination",
                "C_ValidCombination.C_ValidCombination_ID",
                zoomWindow, zoomWindow, zoomQuery);
            _sCacheRefTable.Add(key, retValue.Clone());
            return retValue;
        }
    }
}
